package com.graduationpractice.dbot.controller;


import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.extension.api.R;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.graduationpractice.dbot.biz.IOperationlogService;
import com.graduationpractice.dbot.biz.IUserService;
import com.graduationpractice.dbot.biz.IUsercreatedatabaseService;
import com.graduationpractice.dbot.biz.IUsercreatetableService;
import com.graduationpractice.dbot.pojo.Operationlog;
import com.graduationpractice.dbot.pojo.User;
import com.graduationpractice.dbot.pojo.Usercreatedatabase;
import com.graduationpractice.dbot.pojo.Usercreatetable;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpSession;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

/**
 * <p>
 *  前端控制器
 * </p>
 *
 * @author ${author}
 * @since 2022-03-24
 */
@Controller
@RequestMapping("/user")
public class UserController {

    @Autowired
    IUserService iUserService;
    @Autowired
    IUsercreatetableService iUsercreatetableService;

    @Autowired
    private IOperationlogService iOperationlogService;

    /*登录*/
    //    提交用户名密码，用户登录
    @RequestMapping(value = "/login", method = RequestMethod.POST)
    public String login(User user, HttpSession session) {
        String path = "";
        session.removeAttribute("loginmsg");
        session.removeAttribute("registermsg");
        QueryWrapper<User> queryWrapper = new QueryWrapper();
        queryWrapper.eq("email", user.getEmail());
        queryWrapper.eq("password", user.getPassword());
        User user1 = iUserService.getOne(queryWrapper);

        if (user1 != null) {
            session.setAttribute("user", user1);
            session.setAttribute("check","1");
            path = "redirect:/index";
        } else {


            session.setAttribute("loginmsg", "用户名或密码错误");

            path = "redirect:/login";
        }

        /*侧边栏展示自己的数据库以及表*/
        List<Usercreatetable>usercreatetableList=this.iUsercreatetableService.list(new QueryWrapper<Usercreatetable>().eq("email",user.getEmail()));
        session.setAttribute("MyDatabase",usercreatetableList);

        return path;
    }

    /*用户注册*/
    @RequestMapping(value = "/register", method = RequestMethod.POST)
    public String register(User user, HttpSession session) {
        session.removeAttribute("registermsg");
        QueryWrapper<User> queryWrapper = new QueryWrapper();
        queryWrapper.eq("email", user.getEmail());
        User user1 = iUserService.getOne(queryWrapper);
        if (user1 != null) {
            session.setAttribute("registermsg", "该电子邮箱已被注册");
        } else if (user.getPassword().length() < 6) {
            session.setAttribute("registermsg", "密码太短");
        } else if (user.getUsername().equals("") || user.getEmail().equals("") || user.getPassword().equals("")) {
            session.setAttribute("registermsg", "注册信息不能为空");
        } else {
//            String md5Pass = DigestUtils.md5DigestAsHex(customerService.getPassword().getBytes());
//            customerService.setPassword(md5Pass);
            if (iUserService.save(user)) {
                session.setAttribute("registermsg", "注册成功");
            } else {
                session.setAttribute("registermsg", "注册成功");
            }
        }
        return "redirect:/register";
    }

    /*验证码*/
    @RequestMapping("/checkvercode")
    public String checkVercode(HttpSession session, String vercode) {

        ObjectMapper objectMapper = new ObjectMapper();
        User user = objectMapper.convertValue(session.getAttribute("checkUser"), User.class);
        if (vercode.equals(user.getVercode())) {
            return "redirect:/updatepassword";
        }
        session.removeAttribute("checkVercodemsg");
        session.setAttribute("checkVercodemsg", "验证码有误，请确认后重新输入");
        return "redirect:/checkvercode";
    }

    /*修改密码*/
    @RequestMapping("/updatepassword")
    public String updatePassword(String newpassword,String newpassword1,HttpSession session ) {


        ObjectMapper objectMapper = new ObjectMapper();
        User user = objectMapper.convertValue(session.getAttribute("checkUser"), User.class);

        if (newpassword.equals(newpassword1)&&newpassword.length()>=6) {
            UpdateWrapper<User> userUpdateWrapper =new UpdateWrapper();
            userUpdateWrapper.eq("email",user.getEmail());
            userUpdateWrapper.set("password",newpassword);
            iUserService.update(user,userUpdateWrapper);
            session.removeAttribute("checkUser");
            session.removeAttribute("updatepasswordres");
            session.setAttribute("updatepasswordres","修改成功，请重新登录");
            return "redirect:/login";
        }else if(newpassword.length()<6){
            session.removeAttribute("failupdatepassword");
            session.setAttribute("failupdatepassword","密码过短，请重新输入");
        }else {
            session.removeAttribute("failupdatepassword");
            session.setAttribute("failupdatepassword","两次输入密码不一致，请重新输入");
        }
        return "redirect:/updatepassword";
    }

    /*用户个人信息*/
    @RequestMapping(value = "UserInformation",method = RequestMethod.POST)
    public String showUserInformation(User user,HttpSession session)
    {
        /*展示个人信息*/
        User userinformation;
        userinformation =(User) session.getAttribute("user");
        User user_new=this.iUserService.getById(userinformation.getEmail());

        Operationlog op_log=new Operationlog();
        op_log.setEmail(user.getEmail());
        op_log.setDatabasename("dbotbase");
        op_log.setOperationdate(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss").format(LocalDateTime.now()));

        if(!user_new.getUsername().equals(user.getUsername()))
        {
            op_log.setOperation("更改用户名为"+user.getUsername());
            this.iOperationlogService.save(op_log);
        }

        if(!user_new.getGender().equals(user.getGender()))
        {
            op_log.setOperation("更改性别为"+user.getGender());
            this.iOperationlogService.save(op_log);
        }

        user_new.setEmail(user.getEmail());
        user_new.setGender(user.getGender());
        user_new.setUsername(user.getUsername());
        session.setAttribute("user",user_new);
        this.iUserService.updateById(user_new);



        return "UserInformation";
    }

//    /*用户连接数据库*/
//    @RequestMapping(value = "dblink",method = RequestMethod.POST)
//    public String todblink(String Address,String DataBaseType,String DataBaseName,String user,String password,HttpSession session) throws ClassNotFoundException, SQLException {
//        //session.removeAttribute("link_msg");
//        //加载驱动
//        Class.forName("com.mysql.cj.jdbc.Driver");
//        //用户连接信息
//        String url="jdbc:mysql://"+Address+"/"+DataBaseName+"?serverTimezone=UTC&characterEncoding=utf-8";
////      String url="jdbc:mysql://"+Address+"/"+DataBaseName+"?serverTimezone=UTC&characterEncoding=utf-8";
//        session.setAttribute("Address",Address);
//        String username=user;
//        session.setAttribute("databaseuser",username);
//        String psw=password;
//        session.setAttribute("databasepsw",psw);
//        System.out.println("==========================================");
//        System.out.println(Address);
//        System.out.println(user);
//        System.out.println(password);
////        //连接数据库
////        if ("MySQL".equals((String) session.getAttribute("databasetype"))) {
////            Class.forName("com.mysql.cj.jdbc.Driver");
////            String url = "jdbc:mysql://" + Address + "/?serverTimezone=UTC&characterEncoding=utf-8";
////            DbLink.connectdatabase(url,user,password,session);
////            System.out.println("HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH");
////            System.out.println(session.toString());
////            return "dblink";
////        } else {
////            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
////            String url = "jdbc:sqlserver://" + Address + ";DatabaseName=StdMng2020";
////            DbLink.connectdatabase(url,user,password,session);
////            System.out.println(session.toString());
////            return "dblink";
//        //连接数据库
//        try {
//            Connection connection = DriverManager.getConnection(url, username, psw);
//
//            session.setAttribute("connection", connection);
//            //连接提示
//            if (connection != null) {
//                //获取数据库元数据
//                DatabaseMetaData databaseMetaData = connection.getMetaData();
//                System.out.println("+++++++++++++++++++++++++++++++++++");
//                /*获取数据库中所有的模式*/
//                ResultSet resultSet1 = databaseMetaData.getCatalogs();
//                List<String> schema_list = new ArrayList<>();
//                while (resultSet1.next()) {
//                    System.out.println("数据库名：" + resultSet1.getString("TABLE_CAT"));
//                    schema_list.add(resultSet1.getString("TABLE_CAT"));
//                }
//                session.setAttribute("schema_list", schema_list);
//                session.setAttribute("link_msg", "数据库连接成功！");
//                System.out.println("数据库连接成功");
//
//                //记录日志
//                Operationlog op_log=new Operationlog();
//                User user_1=(User) session.getAttribute("user");
//                op_log.setEmail(user_1.getEmail());
//                op_log.setDatabasename(session.getAttribute("databasename").toString());
//                op_log.setOperation("连接数据库");
//                op_log.setOperationdate(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss").format(LocalDateTime.now()));
//                this.iOperationlogService.save(op_log);
//
//            } else {
//
//                session.setAttribute("link_msg", "数据库连接失败！");
//                System.out.println("数据库连接失败");
//            }
//        }catch (Exception e)
//        {
//            session.setAttribute("link_msg", "数据库连接失败！");
//            e.printStackTrace();
//        }finally {
//            return "dblink";
//        }
//        //创建数据库操作对象
//        //Statement statement=connection.createStatement();
//       // connection.close();
//
//        //return "dblink";
//    }

    /*用户连接数据库*/
    @RequestMapping(value = "dblink", method = RequestMethod.POST)
    public String todblink(String Address, String DataBaseType, String user, String password, HttpSession session) throws ClassNotFoundException, SQLException {
        session.setAttribute("Address", Address);
        String databasetype = DataBaseType;
        session.setAttribute("databasetype", databasetype);
        System.out.println("==========================================");
        System.out.println(Address);
        System.out.println(user);
        System.out.println(password);
        System.out.println(DataBaseType);

        //连接数据库
        if ("MySQL".equals((String) session.getAttribute("databasetype"))) {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://" + Address + "/?serverTimezone=UTC&characterEncoding=utf-8";
            session.setAttribute("url",url);
            connectdatabase(url,user,password,session);
            System.out.println("HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH");
            System.out.println(session.toString());
            return "dblink";
        } else {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String url = "jdbc:sqlserver://" + Address + ";DatabaseName=StdMng2020";
            session.setAttribute("url",url);
            connectdatabase(url,user,password,session);
            System.out.println(session.toString());
            return "dblink";
        }
    }


    /*封装连接数据库的方法*/
    public void connectdatabase(String url, String user, String password, HttpSession session) throws ClassNotFoundException, SQLException {
        String username = user;
        String psw = password;
        session.setAttribute("databaseuser", username);
        session.setAttribute("databasepsw", psw);
        try {
            Connection connection = DriverManager.getConnection(url, username, psw);
            session.setAttribute("connection", connection);
            //连接提示
            if (connection != null) {
                //获取数据库元数据
                DatabaseMetaData databaseMetaData = connection.getMetaData();
                System.out.println("+++++++++++++++++++++++++++++++++++");
                /*获取数据库中所有的模式*/
                ResultSet resultSet1 = databaseMetaData.getCatalogs();
                List<String> schema_list = new ArrayList<>();
                while (resultSet1.next()) {
                    System.out.println("数据库名：" + resultSet1.getString("TABLE_CAT"));
                    schema_list.add(resultSet1.getString("TABLE_CAT"));
                }
                session.setAttribute("schema_list", schema_list);
                session.setAttribute("link_msg", "数据库连接成功！");
                System.out.println("数据库连接成功");
                //记录日志
                Operationlog op_log=new Operationlog();
                User user_1=(User) session.getAttribute("user");
                op_log.setEmail(user_1.getEmail());
                op_log.setDatabasename("");
                op_log.setOperation("连接数据库");
                op_log.setOperationdate(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss").format(LocalDateTime.now()));
                this.iOperationlogService.save(op_log);
            } else {
                session.setAttribute("link_msg", "数据库连接失败！");
                System.out.println("数据库连接失败");
            }
        } catch (Exception e) {
            session.setAttribute("link_msg", "数据库连接失败！");
            System.out.println("==================================================");
            e.printStackTrace();
        }

    }








}

